package com.bj58.groupbuy.action.service.impl;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.model.IndexManager;
import com.bj58.groupbuy.action.model.Menu;
import com.bj58.groupbuy.action.model.ReportManager;
import com.bj58.groupbuy.action.service.IndexManagerService;
import com.bj58.groupbuy.action.util.DateUtil;
import com.bj58.sfft.utility.dao.util.Common;
import com.google.common.collect.Lists;

/** 
 * @author wangchonglu  2016年3月3日 
 *
 */
public class IndexManagerServiceImpl implements IndexManagerService {

	private CommonDao commonDao = new CommonDao();
	static String regEx = "[\u4e00-\u9fa5]";
	static Pattern pat = Pattern.compile(regEx);
	
	@Override
	public List<IndexManager> getIndexManagerDataForSql(String indexInfo, String indexName, String condition)
			throws Exception {
		StringBuffer sql=new StringBuffer();
		sql.append("select *");
		sql.append(" from ir_index");
	    
		if(!("".equals(indexInfo)&&"".equals(indexName))){
			
			if("".equals(indexName)){	    	
				sql.append(" where state=1 and indexInfo like '" +indexInfo+"%'");
			}else{
				sql.append(" where state=1 and indexName like '" +indexName+"%'");
			}		
		}else{
			sql.append(" where state=1 ");
		}
		
		sql.append(" order by indexType DESC ");
		sql.append(" " +condition+" ");
		return commonDao.getAllColsByVo(sql.toString(), IndexManager.class, DBConfig.db_68_bi_sys.getIntType());
	}
	
	@Override
	public int updateData(String indexName, Map<String, String> map) throws Exception {
		 StringBuffer sql = new StringBuffer();
		 sql.append("update ir_index set ");
		 Set<String> keySet = map.keySet();
		 Iterator<String> itr = keySet.iterator();
		 while(itr.hasNext()){
			 String indexName1 = itr.next();
			 //删除，设置失效
			 if("state".equals(indexName1)){
				 sql.append(indexName1+"="+Integer.parseInt(map.get(indexName1)));
			 }else{
			     sql.append(indexName1+"='"+map.get(indexName1)+"'");
			 }
			 sql.append(",");
		 }
		 sql.deleteCharAt(sql.length()-1);
		 sql.append(" where indexName='"+indexName+"'");
		 return commonDao.executeUpdate(sql.toString(),  DBConfig.db_68_bi_sys.getIntType());
	}

    @Override
	public boolean isIndexNameExist(String indexName) throws Exception {
		String sql = "select indexName from ir_index where indexName ='";
		sql += indexName+"'";
		Object obj = commonDao.selectObject(sql, DBConfig.db_68_bi_sys.getIntType());
		if(obj!=null){
			return true;
		}
    	return false;
	}


    @Override
	public int insert(String indexName, String indexInfo,String cate1,String cate2,String indexType, String countRule, String remark,String creater) throws Exception {
	      
    	IndexManager indexManager = new IndexManager();
    	indexManager.setIndexName(indexName);
    	indexManager.setIndexInfo(indexInfo);
    	indexManager.setCate1(cate1);
    	indexManager.setCate2(cate2);
    	indexManager.setIndexType(indexType);
    	indexManager.setCountRule(countRule);
    	indexManager.setRemark(remark);
    	indexManager.setCreater(creater);
	    indexManager.setUpdatetime((DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss")));
	    indexManager.setState(1);
	    
    	List<String> cols =new ArrayList<String>();
 		cols.add("indexName");		
 		cols.add("indexInfo");
 		cols.add("cate1");
 		cols.add("cate2");
 		cols.add("indexType");
 		cols.add("countRule");
 		cols.add("remark");
 		cols.add("creater");
 		cols.add("updatetime");
 		cols.add("state");
 		
 		return commonDao.insertOneByVo(cols,"ir_index",IndexManager.class,indexManager,DBConfig.db_68_bi_sys.getIntType());
    }
	
	

	@Override
	public IndexManager getIndexDataByid(String indexName) throws Exception {
		String sql = "select id,indexName,indexInfo,indexType,cate1,cate2,countRule,remark,creater,updatetime,state from ir_index where indexName='"+indexName+"' OR premIndexName='"+indexName+"'";
		List<IndexManager> ttList=commonDao.getAllColsByVo(sql.toString(), IndexManager.class, DBConfig.db_68_bi_sys.getIntType());
		if(ttList.size()>0)
		{
		  return ttList.get(0);
		}else {
		  return null;
		}
	}


	@Override
	public List<ReportManager> getReportManagerList(Map<String,String> map,String condition)
			throws Exception {
		
		StringBuffer sql=new StringBuffer();
		sql.append("select * from "+map.get("table")+" where ");
		
		if(map.containsKey("tableInfo")||map.containsKey(map.containsKey("indexName"))){
			sql.append("(");
			Set<String> keySet = map.keySet();
			Iterator<String> itr = keySet.iterator();
			int i = 0 ;
			while(itr.hasNext()){
				String key = itr.next();			 
				if("table".equals(key)||"".equals(map.get(key))){
					continue;
				}else {
					if(i>=1){
						sql.append(" and "); 
					}
					sql.append(key+" like '"+map.get(key)+"%'");			     
					i++;
				}
				
			}
			sql .append(") and ");
		}
		
		sql.append("state=1 ");	
		sql.append("order by indexName");
		sql.append(" " +condition+" ");
		return commonDao.getAllColsByVo(sql.toString(), ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public int updateReportData(String tableName, Map<String, String> map) throws Exception {
		 StringBuffer sql = new StringBuffer();
		 sql.append("update "+tableName+" set ");
		 Set<String> keySet = map.keySet();
		 Iterator<String> itr = keySet.iterator();		 	
		 while(itr.hasNext()){
			 String key = itr.next();
			 if("tableInfo".equals(key)||"remark".equals(key)||"tableName".equals(key)){
				 sql.append(key+"='"+map.get(key)+"'");
				 sql.append(",");
			 }
		 }
		 sql.deleteCharAt(sql.length()-1);
		 sql.append(" where id="+map.get("id"));
		 
		
		 return commonDao.executeUpdate(sql.toString(),  DBConfig.db_68_bi_sys.getIntType());
	}


	@Override
	public int insertReportData(String tableName,String indexName,String tableInfo,String cate1,String cate2,String remark) throws Exception {
		ReportManager report = new ReportManager();
		report.setTableName(tableName);
		report.setIndexName(indexName);
		report.setTableInfo(tableInfo);
		report.setCate1(cate1);
		report.setCate2(cate2);
		report.setRemark(remark);
		report.setState(1);
		//设置indexInfo、indexType
		List<String> cols =new ArrayList<String>();
	    String sql = "select indexInfo,indexType from ir_index where indexName='"+indexName+"'";
	    cols.add("indexInfo");
	    cols.add("indexType");
		List<IndexManager> indexList = commonDao.getCommonStatList(sql, cols, IndexManager.class, DBConfig.db_68_bi_sys.getIntType());
 		if(indexList.size()>0){		
 			report.setIndexInfo(indexList.get(0).getIndexInfo());
 			report.setIndexType(indexList.get(0).getIndexType());
 		}
 		cols.clear();
 		
 		//设置tablecate1 tablecate2
 		sql = "select cate1,cate2 from menu where name='报表属性管理'";
 		cols.add("cate1");
	    cols.add("cate2");
	    List<Menu> menu = commonDao.getCommonStatList(sql, cols, Menu.class, DBConfig.db_68_bi_sys.getIntType());
 		if(menu.size()>0){		
 			report.setTablecate1(menu.get(0).getCate1());
 			report.setTablecate2(menu.get(0).getCate2());
 		}
	    
		cols.clear();
		cols.add("tableName");		
 		cols.add("indexName");
 		cols.add("tableInfo");
 		cols.add("cate1");
 		cols.add("cate2");
 		cols.add("indexInfo");
 		cols.add("indexType");
 		cols.add("tablecate1");
 		cols.add("tablecate2");
 		cols.add("remark");
 		cols.add("state");
 		
 		return commonDao.insertOneByVo(cols,"ir_tableindex",ReportManager.class,report,DBConfig.db_68_bi_sys.getIntType());
	}	

	@Override
	public int getTotalCountByTable(Map<String,String> map) throws Exception {
		StringBuffer sql=new StringBuffer();
		sql.append("select COUNT(1) from "+map.get("table"));
		
		if((map.containsKey("indexInfo")||map.containsKey("indexName"))){
			sql.append(" where (");
			Set<String> keySet = map.keySet();
			Iterator<String> itr = keySet.iterator();
			int i = 0 ;
			while(itr.hasNext()){
				String key = itr.next();			 
				if("table".equals(key)||"".equals(map.get(key))){
					continue;
				}else {		 
					if(i>=1&&"ir_index".equals(map.get("table"))){
						sql.append(" or "); 
					}else if(i>=1&&"ir_tableindex".equals(map.get("table"))){
						sql.append(" and ");
					}
					sql.append(key+" like '"+map.get(key)+"%'");			     
					i++;
				}
				
			}
			sql.append(") and state=1");
		}else{
			sql.append(" where state=1 ");
		}
		int count = commonDao.countAll(sql.toString(), DBConfig.db_68_bi_sys.getIntType());
		return count;	
	}


	@Override
	public <T> List<T> getTypeByTypeName(String table, String typeName, Class<T> clazz,String condition) throws Exception {
		String sql = "select DISTINCT "+typeName+" from "+table;
		if(!"".equals(condition)){
			sql += " where "+condition;
		}else{
			sql += " where state=1 ";
		}
		List<String> cols = Lists.newArrayList();
		cols.add(typeName);
		return commonDao.getCommonStatList(sql, cols, clazz, DBConfig.db_68_bi_sys.getIntType());
	}

	@SuppressWarnings("static-access")
	@Override
	public List<String> getReportTypeName(String typeName,String condition) throws Exception {
		
		List<String> list = Lists.newArrayList();
		
		List<ReportManager> report = getTypeByTypeName("ir_index",typeName,ReportManager.class,condition);
		for (ReportManager reportManager : report) {
			Method[] methods = reportManager.getClass().getMethods();
			for (Method method : methods) {
				 String methodName = method.getName().substring(3);
				 Character lower = methodName.charAt(0);
				 methodName = lower.toLowerCase(lower)+methodName.substring(1);
				 if(typeName.equals(methodName)){
					 Field field = ReportManager.class.getDeclaredField(typeName);
					 Method getterMethod = Common.getGetterMethod(ReportManager.class, field);
					 Object obj = getterMethod.invoke(reportManager);
					 if(obj!=null){
						 if("".equals(obj.toString())){
							 continue;
						 }
						 list.add(obj.toString());
					 }
					 break;
				 }
			}
		}
		
		return list;
	}

	@Override
	public List<String> getIndexType(String cate) throws Exception {
		String sql = "select DISTINCT "+cate+" from ir_index where state=1";
		List<String> cols = Lists.newArrayList();
		cols.add(cate);
		List<IndexManager> indexManager =  commonDao.getCommonStatList(sql, cols, IndexManager.class, DBConfig.db_68_bi_sys.getIntType());
		
		List<String> cateList = Lists.newArrayList();
		for (IndexManager indexManager1 : indexManager) {		
			 Field field = IndexManager.class.getDeclaredField(cate);
			 Method getterMethod = Common.getGetterMethod(IndexManager.class, field);
			 Object obj = getterMethod.invoke(indexManager1);
			 if(obj!=null){	
				 if(!"".equals(obj)){					 
					 cateList.add((String)obj);			
				 }
			 }
		}
		
		return cateList;

	}
	@Override
	public List<ReportManager> getReportCate2ByCate1(String Cate1)
			throws Exception {
		// TODO Auto-generated method stub
		String sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
		" from ir_tableindex where state=1 AND cate1='"+Cate1+"' GROUP BY cate2 ORDER BY cate2 ASC";
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<ReportManager> getReportIndexByCate2(String Cate1, String Cate2)
			throws Exception {
		// TODO Auto-generated method stub
		String sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
		" from ir_tableindex where state=1 AND cate1='"+Cate1+"' and cate2='"+Cate2+"' GROUP BY indexType ORDER BY indexType DESC" ;
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<ReportManager> getReportIndexByIndexType(String Cate1,
			String Cate2, String IndexType) throws Exception {
		// TODO Auto-generated method stub
		String sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
		" from ir_tableindex where state=1 AND cate1='"+Cate1
		           +"' and cate2='"+Cate2+"' and indexType='"+IndexType+"' GROUP BY indexName ORDER BY indexName DESC" ;
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<ReportManager> getReportCate1() throws Exception {
		// TODO Auto-generated method stub
		String sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
		" from ir_tableindex where state=1 GROUP BY cate1";
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<ReportManager> getReportById(String id) throws Exception {
		// TODO Auto-generated method stub
		String sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
		" from ir_tableindex where id="+id;
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<ReportManager> getReportByInput(String input) throws Exception {
		// TODO Auto-generated method stub
		Matcher matcher = pat.matcher(input.trim());
		String sql="";
		if (matcher.find())    {//查询指标中文名
		    sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
			" from ir_tableindex where state=1 and indexInfo like '%"+input.trim()+"%'";	
		}else {//查询指标英文名
			sql="select id,indexName,indexInfo,cate1,cate2,tableName,indexType,tableInfo,tablecate1,tablecate2,state,remark" +
			" from ir_tableindex where state=1 and indexName like '%"+input.trim()+"%'";
		}
		return commonDao.getAllColsByVo(sql, ReportManager.class, DBConfig.db_68_bi_sys.getIntType());
	}

	
}

